package com.kwdb.importtool.model;

import lombok.Data;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 表结构模型类
 * 表示数据库表的完整结构定义
 */
@Data
public class TableSchema {
    // 基本信息
    private String tableName;
    private String databaseName;
    private String schemaName;
    private String tableComment;

    // 列定义
    private Map<String, ColumnDefinition> columns;
    private List<String> columnOrder;

    // 约束定义
    private PrimaryKeyConstraint primaryKey;
    private List<ForeignKeyConstraint> foreignKeys;
    private List<UniqueConstraint> uniqueConstraints;
    private List<CheckConstraint> checkConstraints;

    // 索引定义
    private List<IndexDefinition> indexes;

    // 表选项
    private Map<String, String> tableOptions;
    private String engine;
    private String charset;
    private String collation;

    // 统计信息
    private long estimatedRowCount;
    private long actualRowCount;
    private long tableSize;
    private Date createTime;
    private Date updateTime;

    // 迁移相关
    private boolean migrated;
    private String migrationStatus;
    private List<String> migrationErrors;
    private List<String> migrationWarnings;

    public TableSchema() {
        this.columns = new LinkedHashMap<>(); // 保持列顺序
        this.columnOrder = new ArrayList<>();
        this.foreignKeys = new ArrayList<>();
        this.uniqueConstraints = new ArrayList<>();
        this.checkConstraints = new ArrayList<>();
        this.indexes = new ArrayList<>();
        this.tableOptions = new HashMap<>();
        this.migrationErrors = new ArrayList<>();
        this.migrationWarnings = new ArrayList<>();
    }

    public TableSchema(String tableName) {
        this();
        this.tableName = tableName;
    }

    public TableSchema(String tableName, String databaseName) {
        this(tableName);
        this.databaseName = databaseName;
    }

    /**
     * 添加列定义
     */
    public void addColumn(ColumnDefinition column) {
        if (column == null || column.getColumnName() == null) {
            return;
        }

        this.columns.put(column.getColumnName().toLowerCase(), column);
        this.columnOrder.add(column.getColumnName());
    }

    /**
     * 添加列定义（简化版）
     */
    public void addColumn(String columnName, String dataType, int size) {
        addColumn(columnName, dataType, size, 0, true, null, null);
    }

    public void addColumn(String columnName, String dataType, int size, int precision,
                          boolean nullable, String defaultValue, String comment) {
        ColumnDefinition column = new ColumnDefinition();
        column.setColumnName(columnName);
        column.setDataType(dataType);
        column.setSize(size);
        column.setPrecision(precision);
        column.setNullable(nullable);
        column.setDefaultValue(defaultValue);
        column.setComment(comment);

        addColumn(column);
    }

    /**
     * 获取列定义
     */
    public ColumnDefinition getColumn(String columnName) {
        return columns.get(columnName.toLowerCase());
    }

    /**
     * 获取所有列名
     */
    public List<String> getColumnNames() {
        return new ArrayList<>(columnOrder);
    }

    /**
     * 设置主键
     */
    public void setPrimaryKey(String... columnNames) {
        if (columnNames == null || columnNames.length == 0) {
            this.primaryKey = null;
            return;
        }

        this.primaryKey = new PrimaryKeyConstraint();
        this.primaryKey.setConstraintName("PK_" + tableName);
        this.primaryKey.setColumns(Arrays.asList(columnNames));
    }

    /**
     * 设置主键（使用列名列表）
     */
    public void setPrimaryKey(List<String> columnNames) {
        if (columnNames == null || columnNames.isEmpty()) {
            this.primaryKey = null;
            return;
        }

        this.primaryKey = new PrimaryKeyConstraint();
        this.primaryKey.setConstraintName("PK_" + tableName);
        this.primaryKey.setColumns(new ArrayList<>(columnNames));
    }

    /**
     * 添加外键约束
     */
    public void addForeignKey(String constraintName, List<String> columnNames,
                              String referenceTable, List<String> referenceColumns) {
        ForeignKeyConstraint fk = new ForeignKeyConstraint();
        fk.setConstraintName(constraintName);
        fk.setColumns(new ArrayList<>(columnNames));
        fk.setReferenceTable(referenceTable);
        fk.setReferenceColumns(new ArrayList<>(referenceColumns));

        this.foreignKeys.add(fk);
    }

    /**
     * 添加唯一约束
     */
    public void addUniqueConstraint(String constraintName, List<String> columnNames) {
        UniqueConstraint uc = new UniqueConstraint();
        uc.setConstraintName(constraintName);
        uc.setColumns(new ArrayList<>(columnNames));

        this.uniqueConstraints.add(uc);
    }

    /**
     * 添加检查约束
     */
    public void addCheckConstraint(String constraintName, String checkCondition) {
        CheckConstraint cc = new CheckConstraint();
        cc.setConstraintName(constraintName);
        cc.setCheckCondition(checkCondition);

        this.checkConstraints.add(cc);
    }

    /**
     * 添加索引
     */
    public void addIndex(IndexDefinition index) {
        this.indexes.add(index);
    }

    /**
     * 添加索引（简化版）
     */
    public void addIndex(String indexName, List<String> columnNames, boolean unique, boolean primary) {
        IndexDefinition index = new IndexDefinition();
        index.setIndexName(indexName);
        index.setColumns(new ArrayList<>(columnNames));
        index.setUnique(unique);
        index.setPrimary(primary);

        addIndex(index);
    }

    /**
     * 获取创建表的SQL语句
     */
    public String toCreateTableSQL() {
        return toCreateTableSQL(false);
    }

    public String toCreateTableSQL(boolean ifNotExists) {
        StringBuilder sql = new StringBuilder();

        // CREATE TABLE语句
        sql.append("CREATE TABLE ");
        if (ifNotExists) {
            sql.append("IF NOT EXISTS ");
        }
        sql.append(getQualifiedTableName()).append(" (\n");

        // 列定义
        List<String> columnDefinitions = new ArrayList<>();
        for (String columnName : columnOrder) {
            ColumnDefinition column = columns.get(columnName.toLowerCase());
            if (column != null) {
                columnDefinitions.add("    " + column.toSQLDefinition());
            }
        }

        // 主键约束
        if (primaryKey != null && !primaryKey.getColumns().isEmpty()) {
            columnDefinitions.add("    " + primaryKey.toSQLDefinition());
        }

        // 唯一约束
        for (UniqueConstraint uc : uniqueConstraints) {
            columnDefinitions.add("    " + uc.toSQLDefinition());
        }

        // 检查约束
        for (CheckConstraint cc : checkConstraints) {
            columnDefinitions.add("    " + cc.toSQLDefinition());
        }

        sql.append(String.join(",\n", columnDefinitions));
        sql.append("\n)");

        // 表选项
        if (!tableOptions.isEmpty()) {
            sql.append(" ").append(formatTableOptions());
        }

        // 表注释
        if (tableComment != null && !tableComment.isEmpty()) {
            sql.append(" COMMENT '").append(escapeJson(tableComment)).append("'");
        }

        return sql.toString();
    }

    /**
     * 获取限定表名
     */
    public String getQualifiedTableName() {
        if (databaseName != null && !databaseName.isEmpty()) {
            return databaseName + "." + tableName;
        }
        return tableName;
    }

    /**
     * 格式化表选项
     */
    private String formatTableOptions() {
        List<String> options = new ArrayList<>();

        if (engine != null && !engine.isEmpty()) {
            options.add("ENGINE=" + engine);
        }

        if (charset != null && !charset.isEmpty()) {
            options.add("CHARSET=" + charset);
        }

        if (collation != null && !collation.isEmpty()) {
            options.add("COLLATE=" + collation);
        }

        for (Map.Entry<String, String> entry : tableOptions.entrySet()) {
            options.add(entry.getKey() + "=" + entry.getValue());
        }

        return String.join(" ", options);
    }

    /**
     * 比较两个表结构是否相等
     */
    public boolean equals(TableSchema other) {
        if (other == null) return false;

        // 比较基本属性
        if (!Objects.equals(this.tableName, other.tableName)) return false;
        if (!Objects.equals(this.databaseName, other.databaseName)) return false;

        // 比较列定义
        if (this.columns.size() != other.columns.size()) return false;
        for (String columnName : this.columns.keySet()) {
            ColumnDefinition thisColumn = this.columns.get(columnName);
            ColumnDefinition otherColumn = other.columns.get(columnName);

            if (otherColumn == null || !thisColumn.equals(otherColumn)) {
                return false;
            }
        }

        // 比较主键
        if (!Objects.equals(this.primaryKey, other.primaryKey)) return false;

        // 比较约束数量
        if (this.foreignKeys.size() != other.foreignKeys.size()) return false;
        if (this.uniqueConstraints.size() != other.uniqueConstraints.size()) return false;
        if (this.checkConstraints.size() != other.checkConstraints.size()) return false;

        return true;
    }

    /**
     * 获取结构差异
     */
    public SchemaDifference compareWith(TableSchema other) {
        SchemaDifference diff = new SchemaDifference(this.tableName);

        if (other == null) {
            diff.setTablesExist(false);
            return diff;
        }

        // 比较列
        compareColumns(other, diff);

        // 比较主键
        comparePrimaryKeys(other, diff);

        // 比较约束
        compareConstraints(other, diff);

        // 比较索引
        compareIndexes(other, diff);

        return diff;
    }

    /**
     * 比较列定义
     */
    private void compareColumns(TableSchema other, SchemaDifference diff) {
        Set<String> allColumnNames = new HashSet<>();
        allColumnNames.addAll(this.columns.keySet());
        allColumnNames.addAll(other.columns.keySet());

        for (String columnName : allColumnNames) {
            ColumnDefinition thisColumn = this.columns.get(columnName);
            ColumnDefinition otherColumn = other.columns.get(columnName);

            if (thisColumn == null && otherColumn != null) {
                diff.addMissingColumn(columnName);
            } else if (thisColumn != null && otherColumn == null) {
                diff.addExtraColumn(columnName);
            } else if (thisColumn != null && otherColumn != null && !thisColumn.equals(otherColumn)) {
                diff.addColumnDifference(columnName, thisColumn, otherColumn);
            }
        }
    }

    /**
     * 比较主键
     */
    private void comparePrimaryKeys(TableSchema other, SchemaDifference diff) {
        if (this.primaryKey == null && other.primaryKey != null) {
            diff.setPrimaryKeyDifference("Missing primary key");
        } else if (this.primaryKey != null && other.primaryKey == null) {
            diff.setPrimaryKeyDifference("Extra primary key");
        } else if (this.primaryKey != null && other.primaryKey != null &&
                !this.primaryKey.equals(other.primaryKey)) {
            diff.setPrimaryKeyDifference("Primary key mismatch");
        }
    }

    /**
     * 比较约束
     */
    private void compareConstraints(TableSchema other, SchemaDifference diff) {
        // 简化版的约束比较
        if (this.foreignKeys.size() != other.foreignKeys.size()) {
            diff.addConstraintDifference("Foreign key count mismatch");
        }

        if (this.uniqueConstraints.size() != other.uniqueConstraints.size()) {
            diff.addConstraintDifference("Unique constraint count mismatch");
        }
    }

    /**
     * 比较索引
     */
    private void compareIndexes(TableSchema other, SchemaDifference diff) {
        if (this.indexes.size() != other.indexes.size()) {
            diff.addIndexDifference("Index count mismatch");
        }
    }

    /**
     * 添加迁移错误
     */
    public void addMigrationError(String error) {
        this.migrationErrors.add(error);
        this.migrationStatus = "FAILED";
    }

    /**
     * 添加迁移警告
     */
    public void addMigrationWarning(String warning) {
        this.migrationWarnings.add(warning);
    }

    /**
     * 标记迁移成功
     */
    public void markMigrationSuccess() {
        this.migrated = true;
        this.migrationStatus = "SUCCESS";
    }

    /**
     * 获取列数据类型映射
     */
    public Map<String, String> getColumnDataTypes() {
        return columns.entrySet().stream()
                .collect(Collectors.toMap(
                        Map.Entry::getKey,
                        entry -> entry.getValue().getDataType()
                ));
    }

    /**
     * 获取主键列名
     */
    public List<String> getPrimaryKeyColumns() {
        if (primaryKey != null) {
            return new ArrayList<>(primaryKey.getColumns());
        }
        return new ArrayList<>();
    }

    /**
     * 获取索引列名
     */
    public List<String> getIndexedColumns() {
        Set<String> indexedColumns = new HashSet<>();
        for (IndexDefinition index : indexes) {
            indexedColumns.addAll(index.getColumns());
        }
        return new ArrayList<>(indexedColumns);
    }

    /**
     * 列定义类
     */
    @Data
    public static class ColumnDefinition {
        private String columnName;
        private String dataType;
        private int size;
        private int precision;
        private boolean nullable = true;
        private String defaultValue;
        private boolean autoIncrement = false;
        private String comment;

        public String toSQLDefinition() {
            StringBuilder sql = new StringBuilder();
            sql.append(columnName).append(" ").append(dataType);

            // 数据类型大小
            if (size > 0) {
                sql.append("(").append(size);
                if (precision > 0) {
                    sql.append(",").append(precision);
                }
                sql.append(")");
            }

            // 可空性
            if (!nullable) {
                sql.append(" NOT NULL");
            }

            // 默认值
            if (defaultValue != null) {
                sql.append(" DEFAULT ").append(defaultValue);
            }

            // 自增
            if (autoIncrement) {
                sql.append(" AUTO_INCREMENT");
            }

            // 注释
            if (comment != null && !comment.isEmpty()) {
                sql.append(" COMMENT '").append(TableSchema.escapeJson(comment)).append("'");
            }

            return sql.toString();
        }

        @Override
        public boolean equals(Object obj) {
            if (this == obj) return true;
            if (obj == null || getClass() != obj.getClass()) return false;

            ColumnDefinition that = (ColumnDefinition) obj;
            return Objects.equals(columnName, that.columnName) &&
                    Objects.equals(dataType, that.dataType) &&
                    size == that.size &&
                    precision == that.precision &&
                    nullable == that.nullable &&
                    Objects.equals(defaultValue, that.defaultValue) &&
                    autoIncrement == that.autoIncrement;
        }

        @Override
        public int hashCode() {
            return Objects.hash(columnName, dataType, size, precision, nullable, defaultValue, autoIncrement);
        }
    }

    /**
     * 主键约束类
     */
    @Data
    public static class PrimaryKeyConstraint {
        private String constraintName;
        private List<String> columns;

        public String toSQLDefinition() {
            return "PRIMARY KEY (" + String.join(", ", columns) + ")";
        }
    }

    /**
     * 外键约束类
     */
    @Data
    public static class ForeignKeyConstraint {
        private String constraintName;
        private List<String> columns;
        private String referenceTable;
        private List<String> referenceColumns;
        private String onDelete;
        private String onUpdate;

        public String toSQLDefinition() {
            StringBuilder sql = new StringBuilder();
            sql.append("CONSTRAINT ").append(constraintName)
                    .append(" FOREIGN KEY (").append(String.join(", ", columns)).append(")")
                    .append(" REFERENCES ").append(referenceTable)
                    .append(" (").append(String.join(", ", referenceColumns)).append(")");

            if (onDelete != null) {
                sql.append(" ON DELETE ").append(onDelete);
            }

            if (onUpdate != null) {
                sql.append(" ON UPDATE ").append(onUpdate);
            }

            return sql.toString();
        }
    }

    /**
     * 唯一约束类
     */
    @Data
    public static class UniqueConstraint {
        private String constraintName;
        private List<String> columns;

        public String toSQLDefinition() {
            return "CONSTRAINT " + constraintName + " UNIQUE (" + String.join(", ", columns) + ")";
        }
    }

    /**
     * 检查约束类
     */
    @Data
    public static class CheckConstraint {
        private String constraintName;
        private String checkCondition;

        public String toSQLDefinition() {
            return "CONSTRAINT " + constraintName + " CHECK (" + checkCondition + ")";
        }
    }

    /**
     * 索引定义类
     */
    @Data
    public static class IndexDefinition {
        private String indexName;
        private List<String> columns;
        private boolean unique;
        private boolean primary;
        private String indexType; // BTREE, HASH, etc.

        public String toCreateIndexSQL(String tableName) {
            StringBuilder sql = new StringBuilder();

            if (primary) {
                sql.append("PRIMARY KEY");
            } else if (unique) {
                sql.append("UNIQUE INDEX ").append(indexName);
            } else {
                sql.append("INDEX ").append(indexName);
            }

            sql.append(" (").append(String.join(", ", columns)).append(")");

            if (indexType != null) {
                sql.append(" USING ").append(indexType);
            }

            return sql.toString();
        }
    }

    /**
     * 结构差异类
     */
    @Data
    public static class SchemaDifference {
        private String tableName;
        private boolean tablesExist = true;
        private List<String> missingColumns;
        private List<String> extraColumns;
        private Map<String, ColumnDifference> columnDifferences;
        private String primaryKeyDifference;
        private List<String> constraintDifferences;
        private List<String> indexDifferences;

        public SchemaDifference(String tableName) {
            this.tableName = tableName;
            this.missingColumns = new ArrayList<>();
            this.extraColumns = new ArrayList<>();
            this.columnDifferences = new HashMap<>();
            this.constraintDifferences = new ArrayList<>();
            this.indexDifferences = new ArrayList<>();
        }

        public void addMissingColumn(String columnName) {
            this.missingColumns.add(columnName);
        }

        public void addExtraColumn(String columnName) {
            this.extraColumns.add(columnName);
        }

        public void addColumnDifference(String columnName, ColumnDefinition expected, ColumnDefinition actual) {
            this.columnDifferences.put(columnName, new ColumnDifference(expected, actual));
        }

        public void addConstraintDifference(String difference) {
            this.constraintDifferences.add(difference);
        }

        public void addIndexDifference(String difference) {
            this.indexDifferences.add(difference);
        }

        public boolean hasDifferences() {
            return !missingColumns.isEmpty() || !extraColumns.isEmpty() ||
                    !columnDifferences.isEmpty() || primaryKeyDifference != null ||
                    !constraintDifferences.isEmpty() || !indexDifferences.isEmpty();
        }

        @Data
        public static class ColumnDifference {
            private final ColumnDefinition expected;
            private final ColumnDefinition actual;
            private final List<String> differences;

            public ColumnDifference(ColumnDefinition expected, ColumnDefinition actual) {
                this.expected = expected;
                this.actual = actual;
                this.differences = new ArrayList<>();
                findDifferences();
            }

            private void findDifferences() {
                if (!Objects.equals(expected.getDataType(), actual.getDataType())) {
                    differences.add("DataType: " + expected.getDataType() + " vs " + actual.getDataType());
                }
                if (expected.getSize() != actual.getSize()) {
                    differences.add("Size: " + expected.getSize() + " vs " + actual.getSize());
                }
                if (expected.isNullable() != actual.isNullable()) {
                    differences.add("Nullable: " + expected.isNullable() + " vs " + actual.isNullable());
                }
                if (!Objects.equals(expected.getDefaultValue(), actual.getDefaultValue())) {
                    differences.add("DefaultValue: " + expected.getDefaultValue() + " vs " + actual.getDefaultValue());
                }
            }
        }
    }

    @Override
    public String toString() {
        return String.format("TableSchema[name=%s, columns=%d, pk=%s]",
                tableName, columns.size(),
                primaryKey != null ? primaryKey.getColumns() : "none");
    }

    /**
     * JSON转义工具方法
     */
    private static String escapeJson(String str) {
        if (str == null) return "";
        return str.replace("'", "''");
    }
}